<?php

    class Inbox_Leads extends ActiveRecord {    
        
        function _setup()
        {
            $this->table = DB_PREFIX.'inbox_leads';
        }
        
        function validate()
        {
            $this->validates_as_email('email');
            $this->validates_presence_of(array('first_name','last_name'));
        }
        
        function &get_paged_leads($browse)
        {
            $where = '';
            if ( !empty($browse) )
            {
                $where_clause = array();
                // form id
                if ( isset($browse['inbox_form_id']) 
                     && $browse['inbox_form_id'] != 0)
                {
                    $where_clause[] = "l.inbox_form_id = {$browse['inbox_form_id']}";
                }
                
                if ( $browse['year'] != 0
                    || $browse['month'] != 0
                    || $browse['day'] != 0)
                {
                    // date section
                    $year  = (bool) $browse['year']  ? $browse['year']  : '[0-9]{4}';
                    $month = (bool) $browse['month'] ? sprintf("%02d",$browse['month']) : '[0-9]{2}';
                    $day   = (bool) $browse['day']   ? sprintf("%02d",$browse['day'])   : '[0-9]{2}';

                    $where_clause[] = "l.created RLIKE '{$year}-{$month}-{$day} .*'";
                }
                if ( !empty($where_clause) )
                {
                    $where .= ' WHERE '.implode(' AND ',$where_clause);
                }
            }
            
            $sql = "SELECT 
                      l.id, f.form_name, concat(l.first_name,' ',l.last_name) as Name, l.email,
                      date_format(l.created, '%c/%e/%Y %H:%i') as submitted
                    FROM
                      `{$this->table}` l
                    LEFT JOIN `".DB_PREFIX."inbox` f
                    ON
                      f.id = l.inbox_form_id
                    $where
                    ORDER BY l.created DESC
                    ";
            $pager = $this->pager($sql);            
            $pager->header_names = array('form_name'=>'Form Name','email'=>'E-mail');
            $pager->row_key = 'id';
            return $pager;
        }
        
        // These queries have problems in multiuser environments,
        // probably because of the join. problem though, is that leads
        // will be orphaned if you dont do a left join. Hmm how to fix.
        function get_report($id=null)
        {
            if ( !is_null($id) )
            {
                $sql = "SELECT 
                          l.*, f.form_name
                        FROM
                          `{$this->table}` l
                        LEFT JOIN `".DB_PREFIX."inbox` f
                        ON
                          f.id = l.inbox_form_id                          
                        AND
                          l.id = $id
                        ORDER BY l.created DESC
                        ";
            } else {
                $sql = "SELECT 
                          l.*, f.form_name
                        FROM
                          `{$this->table}` l
                        LEFT JOIN `".DB_PREFIX."inbox` f
                        ON
                          f.id = l.inbox_form_id    
                        ORDER BY l.created DESC
                        ";
            }
            include VENDOR.'/adodb/toexport.inc.php';
            $rs = $this->db->Execute($sql);
            return rs2csv($rs);
        }
        
        function get_viewable_lead()
        {
            if ( !$this->is_loaded() )
            {
                return;
            }
            
            return $this->db->GetRow("SELECT 
                      l.*, f.form_name
                    FROM
                      `{$this->table}` l,
                      `".DB_PREFIX."inbox` f
                    WHERE
                     l.id = ?
                     AND
                      f.id = l.inbox_form_id                          
                    ORDER BY l.created DESC
                    ",array($this->get('id')));
        }
    }

?>